﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Maticsoft.DBUtility;
using System.Data;
using System.Data.SqlClient;
using Maticsoft.Model;

namespace Maticsoft.DAL
{
    public class majorDb
    {
        private reportDbHelp reportdbhelp = null;
        public majorDb() 
        {
            reportdbhelp = new reportDbHelp();
        }
        /// <summary>
        /// 选择所有的学院名称
        /// </summary>
        /// <returns></returns>
        public DataTable selectAllCollName() 
        {
            //TODO:选择所有的学院名称
            DataTable dt = new DataTable();
            dt = reportdbhelp.ExecuteQuery("selectAllCollName",CommandType.StoredProcedure);
            return dt;
        }
        /// <summary>
        /// 选择所有的专业名称
        /// </summary>
        /// <returns></returns>
        public DataTable selectAllMajName(string coll_name)
        {
            //TODO:选择所有的专业名称
            DataTable dt = new DataTable();
             SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@coll_name", coll_name)
            };
            dt = reportdbhelp.ExecuteQuery("selectAllmajName",paras ,CommandType.StoredProcedure);
            return dt;
        }
        /// <summary>
        /// 查询所有的专业名称
        /// </summary>
        /// <returns></returns>
        public DataTable selectAllMajName()
        {
            //TODO:选择所有的专业名称
            DataTable dt = new DataTable();
           
            dt = reportdbhelp.ExecuteQuery("selectAllmajName",CommandType.StoredProcedure);
            return dt;
        }
        /// <summary>
        /// 查询所有的管理员信息
        /// </summary>
        /// <returns></returns>
        public DataTable selectAllAdminInfo()
        {
            DataTable dt = new DataTable();
            dt = reportdbhelp.ExecuteQuery("selectAllAdminInfo", CommandType.StoredProcedure);
            return dt;
        }

        /// <summary>
        /// 根据学院名称和专业名称，选择所有的课程
        /// </summary>
        /// <param name="colleage_name">学院名称</param>
        /// <param name="major_name">专业名称</param>
        /// <returns></returns>
        public DataTable selectAllKechengByCollAndMaj(string colleage_name,string major_name)
        {
            DataTable dt = new DataTable();
            SqlParameter[] paras = new SqlParameter[] { 
                 new SqlParameter("@coll_name", colleage_name),
                  new SqlParameter("@major_name", major_name)
            };
            dt = reportdbhelp.ExecuteQuery("selectInfoByCollAndMaj",paras,CommandType.StoredProcedure);
            return dt;
        }
        /// <summary>
        /// 更新课程信息
        /// </summary>
        /// <param name="kecheng_name"></param>
        /// <param name="a"></param>
        /// <param name="c"></param>
        /// <param name="d"></param>
        /// <param name="e"></param>
        /// <param name="f"></param>
        /// <returns></returns>
        public bool updateKEcehnginfo2(string kecheng_name,string a,string c,string d,string e,string f)
        {
            bool flag = false;
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@kecheng_name",kecheng_name),
                new SqlParameter("@a",a),
                new SqlParameter("@c",c),
                new SqlParameter("@d",d),
                new SqlParameter("@e",e),
                new SqlParameter("@f",f)
            };
            int res = reportdbhelp.ExecuteNotQuery("updateKechengByKecName", paras, CommandType.StoredProcedure);
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        } 
        /// <summary>
        /// 更新课程
        /// </summary>
        /// <param name="km"></param>
        /// <returns></returns>
        public bool updateKechengInfo(kechengModel km)
        {
            bool flag = false;
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@kecheng_name",km.kecheng_name),
                new SqlParameter("@a",km.a),
                new SqlParameter("@c",km.a),
                new SqlParameter("@d",km.a),
                new SqlParameter("@e",km.a),
                new SqlParameter("@f",km.a)
            };
            int res = reportdbhelp.ExecuteNotQuery("updateKechengByKechengID", paras, CommandType.StoredProcedure);
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// 更新管理员信息，根据A_Name
        /// </summary>
        /// <param name="zhanghao"></param>
        /// <param name="mima"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        public bool updateAdminInfo(string zhanghao, string mima, string phone)
        {
            bool flag = false;
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@A_Id",zhanghao),
                new SqlParameter("@A_Password",mima),
                new SqlParameter("@A_Name",phone)
            };
            int res = reportdbhelp.ExecuteNotQuery("updateAdminInfo", paras, CommandType.StoredProcedure);
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// 根据课程的ID查找课程所有的信息
        /// </summary>
        /// <param name="kecheng_name"></param>
        /// <returns></returns>
        public DataTable selAllKechengInfoByKeId(string kecheng_name)
        {
            DataTable dt = new DataTable();
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@kecheng_name",kecheng_name)
            };
            dt = reportdbhelp.ExecuteQuery("selectAllKechengInfoByKeId", paras, CommandType.StoredProcedure);
            return dt;
        }
        public DataTable selectAdminNameIsCunZai(string adminname)
        {
            DataTable dt = new DataTable();
            SqlParameter[] paras = new SqlParameter[] { 
                new SqlParameter("@adminName",adminname)
            };
            dt = reportdbhelp.ExecuteQuery("selectAdminNameIsCunZai", paras, CommandType.StoredProcedure);
            return dt;
        }

        /// <summary>
        /// 根据课程的ID查找课程所有的信息
        /// </summary>
        /// <param name="kecheng_name"></param>
        /// <returns></returns>
        public kechengModel sselAllKechengInfoByKeId(string kecheng_name)
        {
            kechengModel mm = new kechengModel();
            DataTable dt = new DataTable();
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@kecheng_name",kecheng_name)
            };
            dt = reportdbhelp.ExecuteQuery("selectAllKechengInfoByKeId",paras,CommandType.StoredProcedure);
            //dt.Rows[0].ItemArray[0].ToString().Trim();
            //(string)mm.id = dt.Rows[0].ItemArray[0].ToString().Trim();
            mm.kecheng_name = dt.Rows[0]["kecheng_name"].ToString();
            mm.a = dt.Rows[0]["a"].ToString();
            mm.a = dt.Rows[0]["c"].ToString();
            mm.a = dt.Rows[0]["d"].ToString();
            mm.a = dt.Rows[0]["e"].ToString();
            mm.a = dt.Rows[0]["f"].ToString();
            mm.major_id = int.Parse((string)dt.Rows[0]["major_id"]);
            return mm;
        }
        /// <summary>
        /// 通过课程ID，删除课程
        /// </summary>
        /// <param name="kecheng_id"></param>
        /// <returns></returns>
        public bool Delete(int kecheng_id)
        {
            // TODO:删除课程
            bool flag = false;
            string cmdText = "deleteKecInfoById";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@kecheng_id",kecheng_id)
            };
            int  res = reportdbhelp.ExecuteNotQuery(cmdText, paras, CommandType.StoredProcedure);
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// 通过管理员id来删除管理员
        /// </summary>
        /// <param name="admin_id"></param>
        /// <returns></returns>
        public bool DeleteAdminInfo(int admin_id)
        {
            // TODO:删除课程
            bool flag = false;
            string cmdText = "deleteAdminInfoById";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@id",admin_id)
            };
            int res = reportdbhelp.ExecuteNotQuery(cmdText, paras, CommandType.StoredProcedure);
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// 通过major_id插入相应专业的课程信息
        /// </summary>
        /// <param name="kecheng_name"></param>
        /// <param name="a"></param>
        /// <param name="c"></param>
        /// <param name="d"></param>
        /// <param name="e"></param>
        /// <param name="f"></param>
        /// <param name="major_id"></param>
        /// <returns></returns>
        public bool insertKechengInfo(string kecheng_name,string a,string c,string d,string e,string f,int major_id)
        {
            bool flag = false;
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@kecheng_name",kecheng_name),
                new SqlParameter("@a",a),
                new SqlParameter("@c",c),
                new SqlParameter("@d",d),
                new SqlParameter("@e",e),
                new SqlParameter("@f",f),
                new SqlParameter("@major_id",major_id),
            };
            int res = reportdbhelp.ExecuteNotQuery("insertKechengInfo",paras,CommandType.StoredProcedure);
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// 添加管理员的信息
        /// </summary>
        /// <param name="user_name"></param>
        /// <param name="password"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        public bool insertAdminInfo(string user_name,string password,string phone)
        {
            bool flag = false;
            SqlParameter[] paras = new SqlParameter[] { 
            new SqlParameter("@user_name",user_name),
            new SqlParameter("@password",password),
            new SqlParameter ("@phone",phone),
            };

            int res = reportdbhelp.ExecuteNotQuery("insertAdminInfo", paras, CommandType.StoredProcedure);
            if (res > 0)
            {
                flag = true;
            }
            return flag;
        }
    }
}
